#include "database.h"

//constructor, with variable initialization
database::database(QObject *parent, QDeclarativeContext *rootCtxt) :
    QObject(parent)
{
    ctxt = rootCtxt;
    dirName = QDir::homePath()+"/.config/tripAccounts";
    tripDir.setPath(dirName);
    if(!tripDir.exists()){
        tripDir.mkpath(dirName);
        qDebug() << "Created Directory: " << tripDir.absolutePath();
    }
    tripsList = tripDir.entryList(QDir::Files,QDir::Time);

    db = QSqlDatabase::addDatabase("QSQLITE");

    exp = new Expense();

    ctxt->setContextProperty("myData", this);
    ctxt->setContextProperty("myExpenses", &expenseM);
    ctxt->setContextProperty("myAccounts", &accountM);
    ctxt->setContextProperty("myNames", names);
    ctxt->setContextProperty("myTrips", tripsList);
    ctxt->setContextProperty("myExpense", exp);
}

//function to create & initialize new trip SQL db file
bool database::newTrip(const QString dbName)
{
    if(db.isOpen()) {
        qDebug() << "Closing Previous trip";
        db.close();
    }
    db.setDatabaseName(dirName+'/'+dbName);
        qDebug() << "Opening db:" << dirName+'/'+dbName;
    if(!db.open()) {
        qDebug() << "Failed to open db:" << dirName+dbName;
        return false;
    }
    QSqlQuery query;
    query.exec("CREATE TABLE account ("
        "id INTEGER PRIMARY KEY,"
        "name STRING,"
        "parties INTEGER)");

    query.exec("CREATE TABLE payment ("
        "id INTEGER PRIMARY KEY,"
        "timestamp DATETIME,"
        "description STRING,"
        "amount FLOAT,"
        "currency_rate FLOAT,"
        "account_id INTEGER,"
        "isDeleted INTEGER DEFAULT 0)");

    query.exec("CREATE TABLE split ("
        "payment_id INTEGER,"
        "account_id INTEGER,"
        "split INTEGER)");

    query.exec("CREATE TABLE property ("
        "name STRING PRIMARY KEY,"
        "value STRING)");

    //initialize last_currencyRate property to 1.0
    query.exec("INSERT INTO property (name,value) VALUES ('last_currencyRate','1.0')");

    //db version, currently 2
    query.exec("PRAGMA user_version=2;");

    loadTrip();

    tripsList = tripDir.entryList(QDir::Files,QDir::Time);
    ctxt->setContextProperty("myTrips", tripsList);

    return true;
}


//function to open trip SQL db file
bool database::openTrip(const QString dbName)
{
    if(db.isOpen()) {
        qDebug() << "Closing Previous trip";
        db.close();
    }
    db.setDatabaseName(dirName+'/'+dbName);
        qDebug() << "Opening db:" << dirName+'/'+dbName;
    if(!db.open()) {
        qDebug() << "Failed to open db:" << dirName+dbName;
        return false;
    }

    loadTrip();

    QSqlQuery query;
    if (query.exec("PRAGMA user_version;")) {
        qDebug() << "Got user Version";
        if (query.first()) {
            qDebug() << "Version is: " << query.value(0).toString();
            if (query.value(0).toString() == "1") {
                //qDebug() << "Adding Property last_currencyRate";
                query.exec("INSERT INTO property (name,value) VALUES ('last_currencyRate','1.0')");
                query.exec("PRAGMA user_version=2;");
            } else {
                qDebug() << "Already at latest Version of db";
            }
        } else {
            qDebug() << "Version is: " << "Null";
        }
    }else {
        qDebug() << "Failed to read user_version from Trip db";
    }
    return true;
}


//function to read trip tables from SQL db
bool database::loadTrip()
{
    expenseM.setQuery("SELECT * FROM payment WHERE isDeleted=0 ");
    accountM.setQuery("SELECT * FROM account");
    getFileList(&names);

    ctxt->setContextProperty("myNames", names);

    return true;
}

//function to edit account in SQL db
bool database::editAccount(const QString id, const QString name, const QString parties)
{
    QSqlQuery query;
    query.prepare("UPDATE account SET name=?, parties=? WHERE id=?");
    query.addBindValue(name);
    query.addBindValue(parties);
    query.addBindValue(id);
    query.exec();
    accountM.setQuery("SELECT * FROM account");
    getFileList(&names);
    ctxt->setContextProperty("myNames", names);
    return true;
}
//function to add account in SQL db
bool database::addAccount(const QString id, const QString name, const QString parties)
{
    QSqlQuery query;
    query.prepare("INSERT INTO account (id, name, parties)"
              "VALUES (?,?,?)");
    query.addBindValue(id);
    query.addBindValue(name);
    query.addBindValue(parties);
    query.exec();
    accountM.setQuery("SELECT * FROM account");
    getFileList(&names);
    ctxt->setContextProperty("myNames", names);

    //Add entries to 'split' table for the new account
    query.exec("SELECT id FROM payment");
    QSqlQuery splitQ;
    while(query.next()) {
        splitQ.prepare("INSERT INTO split (payment_id, account_id, split)"
                "VALUES (?,?,?)");
        splitQ.addBindValue(query.value(0).toString());
        splitQ.addBindValue(id);
        splitQ.addBindValue("0");
        splitQ.exec();
    }
    return true;
}
void database::getFileList(QStringList *names)
{
    names->clear();
    QSqlQuery query("SELECT name FROM account");
    while(query.next())
        names->append(query.value(0).toString());
}


//Function to read Expense from SQL db
bool database::getExpense(int id)
{
    QSqlRecord rec = expenseM.record(id);
    QSqlQuery query;
    query.prepare("SELECT split FROM split WHERE payment_id=? ORDER BY account_id");
    query.addBindValue(id);
    query.exec();
    QString split = "";
    while(query.next()) {
        if(!split.isEmpty()) {
            split += ",";
        }
        split += query.value(0).toString();
    }
    exp->setSplit(split);
    exp->setDescr(rec.value("description").toString());
    exp->setAmnt(rec.value("amount").toString());
    exp->setCrncy(rec.value("currency_rate").toString());
    exp->setPayer(rec.value("account_id").toString());
    QStringList dateTime = rec.value("timestamp").toString().split(" ");
    exp->setDate(dateTime.at(0));
    exp->setTime(dateTime.at(1));
    return true;
}

//Function to create default new Expense
bool database::newExpense()
{
    //exp->setDescr("description");
    //exp->setAmnt("0.00");
    exp->setDescr("");
    exp->setAmnt("");
    QSqlQuery query("SELECT value FROM property WHERE name='last_currencyRate'");
    query.next();
    exp->setCrncy(query.value(0).toString());
    exp->setPayer("1");
    QDateTime now = QDateTime::currentDateTime();
    QStringList dateTime = now.toString("yyyy-MM-dd hh:mm").split(" ");
    //exp->setDate("2011-01-01");
    //exp->setTime("12:00");
    exp->setDate(dateTime.at(0));
    exp->setTime(dateTime.at(1));
    QString split = "";
    for(int i=0;i<accountM.rowCount();i++) {
        if(i>0)
            split += ",";
        split += accountM.record(i).value("parties").toString();
    }
    exp->setSplit(split);
    return true;
}

//function to Add/Edit expense stored in SQl db
bool database::editExpense(bool edit, int expns_id)
{
    QSqlQuery query;
    if(edit) {
        query.prepare("UPDATE payment SET description=?, amount=?,"
               "currency_rate=?, account_id=?, timestamp=? WHERE id=?");
        query.addBindValue(exp->descr());
        query.addBindValue(exp->amnt());
        query.addBindValue(exp->crncy());
        query.addBindValue(exp->payer());
        query.addBindValue(exp->date() + " " + exp->time());
        query.addBindValue(expns_id);
        query.exec();
        QString split = exp->split();
        QStringList splits = split.split(",");
        for (int i = 0; i < splits.size(); i++) {
            query.prepare("UPDATE split SET split=? WHERE payment_id=? AND account_id=?");
            query.addBindValue(splits.at(i));
            query.addBindValue(expns_id);
            query.addBindValue(i);
            query.exec();
        }
    }else {
        query.prepare("INSERT INTO payment (id, description, amount, currency_rate, account_id, timestamp)"
                "VALUES (?,?,?,?,?,?)");
        query.addBindValue(expns_id);
        query.addBindValue(exp->descr());
        query.addBindValue(exp->amnt());
        query.addBindValue(exp->crncy());
        query.addBindValue(exp->payer());
        query.addBindValue(exp->date() + " " + exp->time());
        query.exec();
        QString split = exp->split();
        QStringList splits = split.split(",");
        for (int i = 0; i < splits.size(); i++) {
            query.prepare("INSERT INTO split (payment_id, account_id, split)"
                "VALUES (?,?,?)");
            query.addBindValue(expns_id);
            query.addBindValue(i);
            query.addBindValue(splits.at(i));
            query.exec();
        }
    }
    if (exp->crncy().toFloat() != 1.0) {
        query.prepare("UPDATE property SET value=? WHERE name='last_currencyRate'");
        query.addBindValue(exp->crncy());
        query.exec();
    }
    expenseM.setQuery("SELECT * FROM payment WHERE isDeleted=0 ");
    return true;
}

QStringList database::calcShares(int expns_id)
{
    QSqlQuery query;
    float amount, rate;
    query.prepare("SELECT amount, currency_rate FROM payment WHERE id=? AND isDeleted=0");
    query.addBindValue(expns_id);
    if(query.exec()) {
        query.next();
        amount = query.value(0).toFloat();
        rate   = query.value(1).toFloat();
    }else {
        qDebug() << "Exec failed:" << query.lastError();
        amount = 200;
        rate   = 1.0;
    }
    query.prepare("SELECT split FROM split WHERE payment_id=? ORDER BY account_id");
    query.addBindValue(expns_id);
    query.exec();
    QList<int> splits;
    int peopleCount = 0;
    while(query.next()) {
        int split = query.value(0).toInt();
        splits << split;
        peopleCount += split;
    }
    float perHead = amount*rate/float(peopleCount);
    QStringList shares;
    for (int i=0; i < splits.size(); i++) {
        float share = splits.at(i)*(perHead);
        shares << QString::number(share, 'f', 2);
    }
    return shares;
}

QString database::genSummary()
{
    QSqlQuery query;
    query.exec("SELECT id, account_id, amount, currency_rate FROM payment WHERE isDeleted=0");
    QList<float> expense, paid;
    while(query.next()) {
        int expns_id = query.value(0).toInt();
        int payer    = query.value(1).toInt();        
        float amount = query.value(2).toFloat();
        float rate   = query.value(3).toFloat();
        QStringList shares = calcShares(expns_id);
        if (expense.isEmpty()) {
            for(int i = 0; i < shares.size(); i++) {
                expense << shares.at(i).toFloat();
                if (i == (payer)){
                    paid << amount*rate;
                }else {
                    paid << 0;
                }
            }
        }else {
            for(int i = 0; i < shares.size(); i++) {
                expense[i] += shares.at(i).toFloat();
                if (i == (payer)){
                    paid[i] += amount*rate;
                }else {
                    paid[i] += 0;
                }
            }
        }
    }
    QString summary;
    for(int i = 0; i < expense.size(); i++) {
        if(!summary.isEmpty()) {
            summary += ";";
        }
        summary += QString::number(expense.at(i), 'f', 2);
        summary += ",";
        summary += QString::number(paid.at(i), 'f', 2);
        summary += ",";
        summary += QString::number(paid.at(i)-expense.at(i), 'f', 2);
    }
    return summary;
}

bool database::saveSummary(QString trip)
{
    QString fileName = QDir::homePath() + "/MyDocs/Documents/" + trip + "_Summary.csv";
    QFile csvFile(fileName);
    qDebug() << "Saving Summary to File: " << fileName;
    if (!csvFile.open(QFile::WriteOnly | QFile::Truncate | QFile::Text)) {
        qDebug() << "Could not open File for Writing";
        return false;
    }
    QTextStream out(&csvFile);
    out << "Summary of Trip:," << trip << endl;
    //out << "Result: " << qSetFieldWidth(10) << left << 3.14 << 2.7;
    out << "Account, Expense, Paid, Balance" << endl;

    QSqlQuery query;
    query.exec("SELECT id, account_id, amount, currency_rate FROM payment WHERE isDeleted=0");
    QList<float> expense, paid;
    while(query.next()) {
        int expns_id = query.value(0).toInt();
        int payer    = query.value(1).toInt();        
        float amount = query.value(2).toFloat();
        float rate   = query.value(3).toFloat();
        QStringList shares = calcShares(expns_id);
        if (expense.isEmpty()) {
            for(int i = 0; i < shares.size(); i++) {
                expense << shares.at(i).toFloat();
                if (i == (payer)){
                    paid << amount*rate;
                }else {
                    paid << 0;
                }
            }
        }else {
            for(int i = 0; i < shares.size(); i++) {
                expense[i] += shares.at(i).toFloat();
                if (i == (payer)){
                    paid[i] += amount*rate;
                }else {
                    paid[i] += 0;
                }
            }
        }
    }
    for(int i = 0; i < expense.size(); i++) {
        out << names[i] << ",";
        out << QString::number(expense.at(i), 'f', 2) << ",";
        out << QString::number(paid.at(i), 'f', 2) << ",";
        out << QString::number(paid.at(i)-expense.at(i), 'f', 2) << endl;
    }
    return true;
}

bool database::saveReport(QString trip)
{
    QString fileName = QDir::homePath() + "/MyDocs/Documents/" + trip + "_Report.csv";
    QFile csvFile(fileName);
    qDebug() << "Exporting to File: " << fileName;
    if (!csvFile.open(QFile::WriteOnly | QFile::Truncate | QFile::Text)) {
        qDebug() << "Could not open File for Writing";
        return false;
    }
    QTextStream out(&csvFile);
    out << ",Detailed Report of Trip:," << trip << endl;
    out << "id, Timestamp, Description, Amount, Currency_rate, Payer,," << names.join(",") << endl;
    QSqlQuery query;
    query.exec("SELECT id, timestamp, description, amount, currency_rate, account_id"
           " FROM payment WHERE isDeleted=0");
    while(query.next()) {
        int expns_id = query.value(0).toInt();
        out << expns_id << ",";
        out << query.value(1).toString() << ",";        
        out << query.value(2).toString() << ",";
        out << query.value(3).toString() << ",";
        out << query.value(4).toString() << ",";
        out << names[query.value(5).toInt()] << ",,";

        QStringList shares = calcShares(expns_id);
        out << shares.join(",");
        out << endl;
    }
    return true;
}

bool database::exportCSV(QString trip)
{
    QString fileName = QDir::homePath() + "/MyDocs/Documents/" + trip + ".csv";
    QFile csvFile(fileName);
    qDebug() << "Exporting to File: " << fileName;
    if (!csvFile.open(QFile::WriteOnly | QFile::Truncate | QFile::Text)) {
        qDebug() << "Could not open File for Writing";
        return false;
    }
    QTextStream out(&csvFile);
    out << ",Trip:," << trip << endl;
    out << "id, Timestamp, Description, Amount, Currency_rate, Payer,," << names.join(",") << endl;
    QSqlQuery query;
    query.exec("SELECT id, timestamp, description, amount, currency_rate, account_id"
           " FROM payment WHERE isDeleted=0");
    while(query.next()) {
        int expns_id = query.value(0).toInt();
        out << expns_id << ",";
        out << query.value(1).toString() << ",";        
        out << query.value(2).toString() << ",";
        out << query.value(3).toString() << ",";
        out << query.value(4).toString() << ",";
        out << names[query.value(5).toInt()] << ",";

        QSqlQuery splitQuery;
        splitQuery.prepare("SELECT split FROM split WHERE payment_id=? ORDER BY account_id");
        splitQuery.addBindValue(expns_id);
        splitQuery.exec();
        while(splitQuery.next()) {
            out << ",";
            out << splitQuery.value(0).toString();
        }
        out << endl;
    }
    return true;
}

